using System;
using System.Data;
using Mono.Data.SqliteClient;
using System.Text;
using System.Collections.ObjectModel;
using System.Collections.Generic;
using System.Linq;
using System.IO;

namespace csharp5613
{


	public class Statistics
	{

		public Statistics ()
		{
		}
		
		// Este metodo do tipo dicionário , permite fazer uma consulta á base de dados e
	// Contar os docentes por ano ,  retornando um dicionário com os dados obtidos na consulta
	public static Dictionary<string, int> dicTotalDocAno()
        {
            Dictionary<string, int> list = new Dictionary<string, int>();
            Dictionary<string, int> sortList = new Dictionary<string, int>();
			//Conecção à base de dados
            string connectionString = "URI=file:BaseDadosrebides.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
			//Consulta à Base de dados
            for (int i = 0; i < 10; i++)
            {
                string ano = i.ToString();
                string sql ="SELECT COUNT(DISTINCT id) " +
         					"FROM registo_docentes " +
         					"WHERE registo_docentes.ano = " + ano;
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();
                while (reader.Read())
                {
                    Int32 value = reader.GetInt32(0);
                    // string LastName = reader.GetString (1);
                    Console.WriteLine("Total de Docentes no ano 200" + ano + ": " + value );
                    list.Add("Total de Docentes no ano 200" + ano + ": ", value  );
                }
                reader.Close();
                reader = null;
            }
            //Console.ReadKey();
              
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
		
		
			// Fazer o sort por ordem ascendente
            var sortDic = (from j in list.Keys orderby list[j] ascending select j);

            //Colocar os dados ordenados no sortList
            foreach (string dados in sortDic)
            {
                sortList.Add(dados, list[dados]);
            }


            return sortList;
       }
		
	 // Este metodo do tipo dicionário , permite fazer uma consulta á base de dados e
	// Contar os docentes por estabelecimento e por ano ,  retornando um dicionário com os dados obtidos na consulta
	public static Dictionary<string, Dictionary<string, int>> dicTotalDocEstAno()
        {
            
            Dictionary<string, Dictionary<string, int>> listOne = new Dictionary<string, Dictionary<string, int>>();
			//Conecção à base de dados
            string connectionString = "URI=file:BaseDadosrebides.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
			
			//Consulta à Base de dados
            for (int i = 0; i < 10; i++)
            {
				Dictionary<string, int> listTDE = new Dictionary<string, int>();
          	    Dictionary<string, int> sortlistTDE = new Dictionary<string, int>();
              			
                 string ano = i.ToString();
	        	 string sql =	"SELECT     estabelecimento, COUNT(DISTINCT id) , ano    " +
	                   			"FROM registo_docentes " +
								"WHERE registo_docentes.ano = " + ano + " " +
	                  			"GROUP BY registo_docentes.estabelecimento";
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();
                Console.WriteLine("Total Docentes por estabelecimento no 200{0}:", i);
                while (reader.Read())
                {
                    string estabelecimento = reader.GetString(0);
                    Int32 value = reader.GetInt32(1);
                    Console.WriteLine(estabelecimento + ": " + value);
                    listTDE.Add(estabelecimento + ": ", value);
                }
                reader.Close();
                reader = null;

                // Fazer o sort por ordem ascendente
               var dados = from j in listTDE.Keys orderby listTDE[j] ascending select j;

                //Colocar os dados ordenados no sortList
                foreach (string estab in dados)
                {
                    sortlistTDE.Add(estab, listTDE[estab]);
                    Console.WriteLine(estab + sortlistTDE[estab]);
                }
			

                listOne.Add("Ano 200"+ i, sortlistTDE);
                Console.WriteLine("Total Docentes por estabelecimento no ano 200{0} : ", i);
                Console.WriteLine();
            }
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
            return listOne;
        }
		
		
	
		
		
		
		
	// Este metodo do tipo dicionário , permite fazer uma consulta á base de dados e
	// Contar os docentes por grau e por ano ,  retornando um dicionário com os dados obtidos na consulta
	public static	Dictionary<string, Dictionary<string, int>> dicTotalDocGrauAno()
        {
            Dictionary<string, int> listOne = new Dictionary<string, int>();
            Dictionary<string, int> sortListOne = new Dictionary<string, int>();
            Dictionary<string, Dictionary<string, int>> listTwo = new Dictionary<string, Dictionary<string, int>>();
			//Conecção à base de dados
            string connectionString = "URI=file:BaseDadosrebides.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
			//Consulta à Base de dados
            for (int i = 0; i < 10; i++)
            {
                listOne.Clear();
                sortListOne.Clear();
                 string ano = i.ToString();
	        	 string sql =
								"SELECT     grau, COUNT(id)     " +
                  			 	  "FROM registo_docentes " +
								 "WHERE registo_docentes.ano = " + ano + " " +
                  			   "GROUP BY registo_docentes.grau";
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();
                Console.WriteLine("Totaol de docentes por grau no ano 200{0}:", i);
                while (reader.Read())
                {
                    string grau = reader.GetString(0);
                    Int32 value = reader.GetInt32(1);
                    Console.WriteLine(grau + ": " + value);
                    listOne.Add(grau + ": ", value);
                }
                reader.Close();
                reader = null;

                // Fazer o sort por ordem ascendente
                var dados = from j in listOne.Keys orderby listOne[j] ascending select j;

                //Colocar os dados ordenados no sortList
                 foreach (string estab in dados)
                  {
                     sortListOne.Add(estab, listOne[estab]);
                     Console.WriteLine(estab + sortListOne[estab]);
              	  }

                listTwo.Add("Ano 200"+ i,sortListOne);
                Console.WriteLine("Total Docentes por grau no ano 200{0} : ", i);
                Console.WriteLine();
            }
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
            return listTwo;
        }
		
		// Este metodo do tipo dicionário , permite fazer uma consulta à base de dados e
		// Contar o total de docentes por grau, por estabelecimento e por ano ,  retornando 
		// um dicionário com os dados obtidos na consulta
	public static    Dictionary<string, Dictionary<string, int>> dicTotalDocGrauEstAno()
        {
            
            Dictionary<string, Dictionary<string, int>> listTwo = new Dictionary<string, Dictionary<string, int>>();
			//Conecção à base de dados
            string connectionString = "URI=file:BaseDadosrebides.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
			// Consulta à base de dados
            for (int i = 0; i < 10; i++)
            {
                Dictionary<string, int> listOne = new Dictionary<string, int>();
            	Dictionary<string, int> sortListOne = new Dictionary<string, int>();
                 string ano = i.ToString();
	        	 string sql =
								"SELECT     grau, COUNT(id)     " +
                  			 	  "FROM registo_docentes " +
								 "WHERE registo_docentes.ano = " + ano + " " +
                  			   "GROUP BY registo_docentes.grau";
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();
                Console.WriteLine("Totaol de docentes por grau no ano 200{0}:", i);
                while (reader.Read())
                {
                    string grau = reader.GetString(0);
                    Int32 value = reader.GetInt32(1);
                    Console.WriteLine(grau + ": " + value);
                    listOne.Add(grau + ": ", value);
                }
                reader.Close();
                reader = null;

                // Fazer o sort por ordem ascendente
                var dados = from j in listOne.Keys orderby listOne[j] ascending select j;

               //Colocar os dados ordenados no sortList
                foreach (string estab in dados)
                {
                    sortListOne.Add(estab, listOne[estab]);
                    Console.WriteLine(estab + sortListOne[estab]);
                }

                listTwo.Add("Ano 200"+ i, sortListOne);
                Console.WriteLine("Total Docentes por grau no ano 200{0} : ", i);
                Console.WriteLine();
            }
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
            return listTwo;
        }
		
	  
	
	// Estatisticas Adicionais
	// Numero dos titulares do grau Doutorado por estabelecimento e por ano
	public static Dictionary<string, Dictionary<string, int>> dicTotalDocDoutEstAno( string grau)
        {
            
            Dictionary<string, Dictionary<string, int>> listTwo = new Dictionary<string, Dictionary<string, int>>();
			//Conecção à base de dados
            string connectionString = "URI=file:BaseDadosrebides.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
			//Consulta à Base de dados
            for (int i = 0; i < 10; i++)
            {
               Dictionary<string, int> listOne = new Dictionary<string, int>();
            Dictionary<string, int> sortListOne = new Dictionary<string, int>();
                 string ano = i.ToString();
				
	        	 string sql =	"SELECT  estabelecimento,  COUNT(id) " +
								"FROM registo_docentes " +
								"WHERE grau LIKE '" + grau +  "' AND ano = " + ano +
								" GROUP BY registo_docentes.estabelecimento";

                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();
                Console.WriteLine("Total Docentes com o grau Doutoramento por estabelecimento no 200{0}:", i);
                while (reader.Read())
                {
                    string estabelecimento = reader.GetString(0);
                    Int32 value = reader.GetInt32(1);
                    Console.WriteLine(estabelecimento + ": " + value);
                    listOne.Add(estabelecimento + ": ", value);
                }
                reader.Close();
                reader = null;

                // Fazer o sort por ordem ascendente
               var dados = from k in listOne.Keys orderby listOne[k] ascending select k;

                //Colocar os dados ordenados no sortListOne
                foreach (string estab in dados)
                {
                    sortListOne.Add(estab, listOne[estab]);
                    Console.WriteLine(estab + sortListOne[estab]);
                }

                listTwo.Add("Ano 200"+ i, listOne);
                Console.WriteLine("Total Docentes com o grau Doutoramento por estabelecimento no 200{0}: ", i);
                Console.WriteLine();
            }
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
            return listTwo;
        }
		
	// Conjunto dos titulares do grau Doutorado por estabelecimento e por ano
		
	//#######################
	public static Dictionary<string, Dictionary<string, string>> dicNomeDocDoutEstAno( string grau)
        {
           
            Dictionary<string, Dictionary<string, string>> listTwo = new Dictionary<string, Dictionary<string, string>>();
			//Conecção à base de dados
            string connectionString = "URI=file:BaseDadosrebides.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
			//Consulta à Base de dados
            for (int i = 0; i < 10; i++)
            {
                 Dictionary<string, string> listOne = new Dictionary<string, string>();
           		 Dictionary<string, int> sortListOne = new Dictionary<string, int>();
                 string ano = i.ToString();
				
	        	 string sql =	"SELECT  estabelecimento,  docente " +
								"FROM registo_docentes " +
								"WHERE grau LIKE '" + grau +  "' AND ano = " + ano +
								" GROUP BY registo_docentes.estabelecimento";

                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();
                Console.WriteLine("Conjunto dos titulares do grau Doutorado por estabelecimento e por ano 200{0}:", i);
                while (reader.Read())
                {
					
                    string estabelecimento = reader.GetString(0);
                    string nomeDocente = reader.GetString(1);
				
                    Console.WriteLine(estabelecimento + ": " + nomeDocente);
                    listOne.Add(estabelecimento + ": ", nomeDocente);
                }
                reader.Close();
                reader = null;

                
      

                listTwo.Add("Ano 200"+ i, listOne);
                Console.WriteLine("Conjunto dos titulares do grau Doutorado por estabelecimento e por ano 200{0}: ", i);
                Console.WriteLine();
            }
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
            return listTwo;
        }	
		
		
		
		
 	// LISTAS		
 	//#################################################################################
 	// Este metodo devolve uma lista com os estabelecimentos por ano
	 public static Dictionary<string, List<string> >listEstabAno()
	
	{
      
	   List<string> listEstabAno = new List<string>();	
		Dictionary<string, List<string>> listTwoProfEstabAno = new Dictionary<string, List<string>>();	
	   //Conecção à Base de dados
	   string connectionString = "URI=file:BaseDadosrebides.db,version=3";
       IDbConnection dbcon;
       dbcon = (IDbConnection) new SqliteConnection(connectionString);
       dbcon.Open();
       IDbCommand dbcmd = dbcon.CreateCommand();
       //Consulta à Base de dados
		//Console.WriteLine("Criaçao de uma lista com os  estabelecimentos por ano:");		
     	for (int i = 0; i < 10; i++)
        {
		  
		 //Dictionary<string, int> list = new Dictionary<string, int>();	
         string ano = i.ToString();
         string sql =
			"SELECT     estabelecimento, COUNT(id) , ano    " +
                     "FROM registo_docentes " +
					 "WHERE registo_docentes.ano = " + ano + " " +
                     "GROUP BY registo_docentes.estabelecimento";
         dbcmd.CommandText = sql;
         IDataReader reader = dbcmd.ExecuteReader();
		 
	    Console.WriteLine("Criaçao de uma lista com os estabelecimentos no ano 200{0}", i);		
				
		 while (reader.Read())
         {
		               
                    string estabelecimento = reader.GetString(0);
                    //Int32 value = reader.GetInt32(1);
                   // Console.WriteLine( estabelecimento +" - "+ "Total Docentes:" + value);
				
                    listEstabAno.Add(estabelecimento );
         }
			
         reader.Close();
    	 reader = null;   
		 listTwoProfEstabAno.Add("No ano 200" + ano + ": ", listEstabAno);
		
		 // Escrita na consola 
		 foreach(string k in listEstabAno)
			{
			   Console.WriteLine(k);	
			}
		 Console.WriteLine("no Ano 200{0}" , i);
        }
		
        dbcmd.Dispose();
        dbcmd = null;
        dbcon.Close();
        dbcon = null;
		return listTwoProfEstabAno;
		
	}
		
	// Este Metodo retorna uma lista com os titulares dos grau e por ano
	// É feita uma consulta à base de dados , sao colados numa string os nomes dos docentes por grau e por ano
	public static Dictionary<string, List<string> >listDocGrauAno()
	
	{
      
	   List<string> listDocGrauAno = new List<string>();
	   List<string> grau = new List<string>();
	   grau.Add("Licenciatura");
	   Dictionary<string, List<string>> listTwoDocGrauAno = new Dictionary<string, List<string>>();	
	   //Conecção à Base de dados
	   string connectionString = "URI=file:BaseDadosrebides.db,version=3";
       IDbConnection dbcon;
       dbcon = (IDbConnection) new SqliteConnection(connectionString);
       dbcon.Open();
       IDbCommand dbcmd = dbcon.CreateCommand();
       //Console.WriteLine("Criaçao de uma lista com os  estabelecimentos por ano:");		
     	//Consulta à base de dados
		for (int i = 0; i < 10; i++)
        {
		  
		 //Dictionary<string, int> list = new Dictionary<string, int>();	
         string ano = i.ToString();
	    
 		 string sql =
			"SELECT    DISTINCT(docente)    " +
                     "FROM registo_docentes " +
					 "WHERE registo_docentes.ano = " + ano + " " +
                     "GROUP BY registo_docentes.grau"  ;
         dbcmd.CommandText = sql;
         IDataReader reader = dbcmd.ExecuteReader();
		 
	    Console.WriteLine("Criaçao de uma lista com os professores com grau no ano 200{0}", i);		
				
		 while (reader.Read())
         {
		               
                    string docente = reader.GetString(0);
                    //Int32 value = reader.GetInt32(1);
                   // Console.WriteLine( estabelecimento +" - "+ "Total Docentes:" + value);
				
                    listDocGrauAno.Add(docente );
         }
         reader.Close();
    	 reader = null;   
		 listTwoDocGrauAno.Add("Lista de Professores com o grau Mestrado, no ano 200" + ano + ": ", listDocGrauAno);
		 foreach(string k in listDocGrauAno)
			{
			   Console.WriteLine(k);	
			}
       }
       dbcmd.Dispose();
       dbcmd = null;
       dbcon.Close();
       dbcon = null;
		return listTwoDocGrauAno;
		
	}
		
   
	//------------------------------------------------------------------
		// Este metodo permite consultar numero de pessoal que se encontra nos estabelecimentos num determinado ano
		// O metodo leva como parametro o ano.
		// Para ser possivel usar a List do tipo (string , string , int)  , houve necessidade de criar uma classe "SaveList"
		public static List<csharp5613.SaveList> tDEA(int ano)
        {
            List<csharp5613.SaveList> list = new List<csharp5613.SaveList>();
			//Conecção à Base de dados
			string connectionString = "URI=file:BaseDadosrebides.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();


            list.Clear();
            //consulta à Base de dados
			string a = ano.ToString();
            string sql = "SELECT estabelecimento, COUNT(DISTINCT id) " +
                            "FROM registo_docentes " +
                            "WHERE registo_docentes.ano = " + a + " " +
                            "GROUP BY registo_docentes.estabelecimento " ;
                            
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();

            while (reader.Read())
            {
                string estabelecimento = reader.GetString(0);
                Int32 value = reader.GetInt32(1);
				list.Add(new csharp5613.SaveList(estabelecimento,value));
                
            }

            reader.Close();
            reader = null;

            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
           
            return list;
        } 
		
	 // Este metodo devolve uma lista com o pessoal a deixar as instituiçoes por ano
	 //	Recebe como parametro "file" para ser possivel escrever o ficheiro txt
     public static Dictionary<string, List<csharp5613.SaveList >> listDDEAno(System.IO.StreamWriter file)
	 {
		Dictionary<string, List<csharp5613.SaveList >> finalList = new Dictionary<string, List<csharp5613.SaveList >>();
        List<csharp5613.SaveList > listTDDeixEstA = new List<csharp5613.SaveList >();
         List<csharp5613.SaveList > sortListTDDeixEstA = new List<csharp5613.SaveList>();
		//Conecção à base de dados
		string connectionString = "URI=file:BaseDadosrebides.db,version=3";
        IDbConnection dbcon;
        dbcon = (IDbConnection)new SqliteConnection(connectionString);
        dbcon.Open();
        IDbCommand dbcmd = dbcon.CreateCommand();
	    file.WriteLine("-------------------------------------------------------------------");
		file.WriteLine("");
		file.WriteLine("Lista do numero de pessoas que deixaram os estabelecimentos por ano");
        file.WriteLine("");
		//Consulta à base de dados
		for (int i = 1; i < 10; i++)
        {
          Console.WriteLine("No Ano 200{0}", i);
         
          List<csharp5613.SaveList > list = csharp5613.Statistics.tDEA(i);
          List<csharp5613.SaveList > listASeguinte = csharp5613.Statistics.tDEA(i+1);
    	  foreach (csharp5613.SaveList l in list)
          {
             foreach (csharp5613.SaveList listAS in listASeguinte)
             {
               	 if (l.estab == listAS.estab)
                {
                 if (l.tDocentes > listAS.tDocentes)
                     {
                           int nDocentes = l.tDocentes - listAS.tDocentes;
								
                           listTDDeixEstA.Add(new csharp5613.SaveList(l.estab , nDocentes));
						    
                      }
							
                }
             }
					
           }

             
				listTDDeixEstA.Sort(delegate(csharp5613.SaveList docentes1, csharp5613.SaveList docentes2) { return docentes1.tDocentes.CompareTo(docentes2.tDocentes); });
				finalList.Add("No ano 200" + i, listTDDeixEstA);
				file.WriteLine("No ano 200"+i);
				file.WriteLine("");
				foreach ( csharp5613.SaveList ss in listTDDeixEstA)
                {
					
					file.WriteLine(ss);
					Console.WriteLine(ss);
   
                }
				
                Console.WriteLine("No ano 200{0}", i);
                Console.WriteLine();
            }
			
            return finalList;
	}
		
	   // Lista do numero de pessoas que entraram nos estabelecimentos por ano
	   // Recebe como parametro o "file" para ser possivel escrever um ficheiro .txt
	   public static Dictionary<string, List<csharp5613.SaveList >> listDEEA(System.IO.StreamWriter file)
        {

            Dictionary<string, List<csharp5613.SaveList > > finalList = new Dictionary<string, List<csharp5613.SaveList > >();
			List<csharp5613.SaveList > listNDEstA = new List<csharp5613.SaveList >();
            List<csharp5613.SaveList > sortListNDEstA = new List<csharp5613.SaveList >();
            //Conecção à base de dados
			string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
			file.WriteLine();
			file.WriteLine("Lista do numero de pessoas que entraram nos estabelecimentos por ano");
			file.WriteLine();
            //Consulta à base de dados
			for (int i = 1; i < 10; i++)
            {
                Console.WriteLine("No ano 200{0}", i);
                
                List<csharp5613.SaveList > listAnterior = csharp5613.Statistics.tDEA(i-1);
                List<csharp5613.SaveList > list = csharp5613.Statistics.tDEA(i);

                foreach (csharp5613.SaveList l in list)
                {
                    foreach (csharp5613.SaveList listA in listAnterior)
                    {
                        if (l.estab == listA.estab)
                        {
                            if (l.tDocentes > listA.tDocentes)
                            {
                                int nDocentes = l.tDocentes - listA.tDocentes;
                                listNDEstA.Add(new csharp5613.SaveList(l.estab, nDocentes));
                            }
                        }
                    }
                }
				listNDEstA.Sort(delegate(csharp5613.SaveList docentes1, csharp5613.SaveList docentes2) { return docentes1.tDocentes.CompareTo(docentes2.tDocentes); });
           
				file.WriteLine();
				file.WriteLine("No ano 200"+i);
				file.WriteLine();
				foreach ( csharp5613.SaveList ss in listNDEstA)
                {
					
					file.WriteLine(ss);
					Console.WriteLine(ss);
                   
                }
                finalList.Add("No ano 200" + (i), listNDEstA);

                Console.WriteLine("NO ano 200{0}", i);
                Console.WriteLine();
            }

            return finalList;
        } 
		
		
		
		
	}
}
